package com.dnbns.lib;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBbase extends SQLiteOpenHelper{
	public static final String DATABASE_NAME = "BODYWATER.db";
	public static final int DATABASE_VERSION = 4;
	
	public static final String TABLE_DBINFO = "DBInfo";
	public static final String COLUMN_VERSION = "version";
	public static final String COLUMN_DOWNLOADDATE = "ddate";

	/** Water Data */
	public static final String TABLE_WATER	 				= "WATER";
	public static final String KEY_WATER_IDX				= "idx";
	public static final String KEY_WATER_WEIGHT				= "weight";
	public static final String KEY_WATER_SEX				= "sex";
	public static final String KEY_WATER_BIRTH				= "birth";
	public static final String KEY_WATER_CUP_TYPE			= "cup_type";
	public static final String KEY_WATER_CUP_VOL			= "cup_vol";
	public static final String KEY_WATER_YEAR				= "year";
	public static final String KEY_WATER_MONTH				= "month";
	public static final String KEY_WATER_DAY				= "day";
	public static final String KEY_WATER_HOUR				= "hour";
	public static final String KEY_WATER_MINUTE				= "minute";
	public static final String KEY_WATER_SECOND				= "second";
	public static final String KEY_WATER_FLAG				= "flag";
	public static final String KEY_WATER_SERVER				= "server";
	
	public DBbase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);		
	}
	
	public DBbase(Context context, String name) {
		super(context, name, null, DATABASE_VERSION);
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		createTable(db);
	}
	
	public void dropTable(SQLiteDatabase db, String tableName){
		String sql = "drop table " + tableName;
		db.execSQL(sql);
	}
	
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
		if(oldVersion <= 1) {
			String query = "ALTER TABLE TB_WORK ADD args TEXT";
			db.compileStatement(query).execute();
		}
		
		if(oldVersion <= 2) {
			String query = "ALTER TABLE TB_WORK ADD flag TEXT";
			db.compileStatement(query).execute();
		}
		
		if(oldVersion <= 3) {
			String query = "ALTER TABLE TB_WORK ADD odr TEXT";
			db.compileStatement(query).execute();
		}
	}
	
	public void createTable(SQLiteDatabase _db){

		String sql_TABLE_WATER = 
			"create table " + DBbase.TABLE_WATER + 
			"(" +

				KEY_WATER_IDX				+ " integer primary key autoincrement, "+
				KEY_WATER_WEIGHT			+ " int, " +
				KEY_WATER_SEX				+ " boolean, " +
				KEY_WATER_BIRTH				+ " text, " +
				KEY_WATER_CUP_TYPE			+ " text, " +
				KEY_WATER_CUP_VOL			+ " int, " +
				KEY_WATER_YEAR				+ " int, " +
				KEY_WATER_MONTH				+ " int, " +
				KEY_WATER_DAY				+ " int, " +
				KEY_WATER_HOUR				+ " int, " +
				KEY_WATER_MINUTE			+ " int, " +
				KEY_WATER_SECOND			+ " int, " +
				KEY_WATER_FLAG				+ " int, " +
				KEY_WATER_SERVER			+ " int " +
				
			")";
		
		_db.execSQL(sql_TABLE_WATER);
	}
	
	public synchronized JSONArray execute(String query) {
		SQLiteDatabase db = getReadableDatabase();
		String sql = query;
		Cursor cursor = db.rawQuery(sql, null);
		JSONArray objA = new JSONArray();

		try {
			int columnCount = cursor.getColumnCount();
			while(cursor.moveToNext()){
				JSONObject obj = new JSONObject();
				for(int i=0; i<columnCount; i++){
					String name = null;
					String value = null;
					try{
						name = cursor.getColumnName(i);
						value = cursor.getString(i);
					}catch(NullPointerException e){
						e.printStackTrace();
						Log.i("TEST", "Exception query : " + query);
					}
					if(name != null){
						if(value == null){
							value = "";
						}
						obj.put(name, value);
					}
				}
				objA.put(obj);
			}	
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			cursor.close();
			db.close();
		}
		
		return objA;
	}
}


